package com.yss.fengxin.work;

import java.sql.*;
import java.util.Random;

public class SQLOperation {

    Connection conn;

    public SQLOperation()   {
        try {

            // Register JDBC driver.
            Class.forName("org.apache.ignite.IgniteJdbcThinDriver");
            // Open JDBC connection.
            //conn = DriverManager.getConnection("jdbc:ignite:thin://127.0.0.2/");
            conn = DriverManager.getConnection("jdbc:ignite:thin://127.0.0.1/");
        }catch (SQLException e){
            System.out.println(e.getErrorCode());
        }catch (ClassNotFoundException e){
            System.out.println(e.getCause());
        }

    }

    public void closeConnection() throws SQLException {
        conn.close();
    }


    public void createPriceTable() throws SQLException {

        // Create database tables.
        try (Statement stmt = conn.createStatement()) {
            // Drop index
            stmt.executeUpdate("DROP INDEX IF EXISTS idx_price_symbol ON position  ");
            // Drop table
            stmt.executeUpdate("DROP TABLE IF EXISTS Price");
            // Create table based on PARTITIONED template with one backup.
            stmt.executeUpdate("CREATE TABLE Price (" +
                    " symbol VARCHAR, price DOUBLE," +
                    " PRIMARY KEY (symbol)) " +
                    " WITH \"TEMPLATE=MyTemplate, affinityKey=symbol\"");

            // Create an index on the Postion table.
            stmt.executeUpdate("CREATE INDEX idx_price_symbol ON position (symbol)");

        }
    }
    public void insertToPriceTable() throws SQLException {

        // Populate Position table
        try (PreparedStatement stmt =
                     conn.prepareStatement("INSERT INTO price ("+
                             " symbol," +
                             " price) VALUES (?, ?)")) {

            long seed = System.currentTimeMillis();
            Random rand = new Random(seed);
            int symbolNum = 3000;
            for (int i = 0; i < symbolNum; i++) {
                    stmt.setString(1, "symbol" + i);
                    stmt.setDouble(2, rand.nextInt(1000)/100.0);
                    stmt.executeUpdate();
                }
        }


    }
    public void updatePriceTable() throws SQLException {
        long startNanoTime = 0;
        long finishNanoTime =0;
        String symbol="symbol1681";
        double price = 0.0;
        double ms = 0.0;
        double totalms = 0.0;
        double randPrice = 10.2;
        double  speed = 1;
        String msg = "";
        long seed = System.currentTimeMillis();
        Random rand = new Random(seed);
        int symbolNum = 3000;

        try (PreparedStatement stmt =
                conn.prepareStatement("Update Price set price = ? Where symbol = ?")) {
            startNanoTime = System.nanoTime();
            for (int j = 0; j < symbolNum; j++) {
                //symbol =  "symbol" + j;
                //price = rand.nextInt(1000)/100.0;
                stmt.setDouble(1, price);
                stmt.setString(2, symbol);


                stmt.executeUpdate();
            }
            finishNanoTime = System.nanoTime();
            ms = finishNanoTime-startNanoTime==0 ? 1/1000000.0 : (finishNanoTime-startNanoTime)/1000000.0;
            speed = Math.round(symbolNum*100000/ms)/100.0;
            msg = "update Price "+symbolNum+" row, total time spent: "+String.format("%010.2f", ms)+" ms, "+speed+"/s";
            System.out.println(msg);
        }
        //conn.close();
    }


    public void createPositionTable() throws SQLException {
        // Create database tables.
        try (Statement stmt = conn.createStatement()) {
            // Drop index
            stmt.executeUpdate("DROP INDEX IF EXISTS idx_postion_symbol_product ON position");
            // Drop table
            stmt.executeUpdate("DROP TABLE IF EXISTS position ");
            // Create table based on PARTITIONED template with one backup.
            stmt.executeUpdate("CREATE TABLE position (" +
                    " product VARCHAR, symbol VARCHAR, industry VARCHAR, volume INT," +
                    " PRIMARY KEY (symbol,product)) " +
                    " WITH \"TEMPLATE=MyTemplate, affinityKey=product\"");
            // Create an index on the Postion table.
            stmt.executeUpdate("CREATE INDEX idx_postion_symbol_product ON position (symbol,product)");

        }
    }
    public void insertToPositionTable() throws SQLException {

        // Populate Position table
        try (PreparedStatement stmt =
                     conn.prepareStatement("INSERT INTO position (" +
                             " product," +
                             " symbol," +
                             " industry," +
                             " volume )" +
                             " VALUES ( ?, ?, ?, ?)")) {

            long seed = System.currentTimeMillis();
            Random rand = new Random(seed);
            int productNum = 1000;
            int industryNum = 60;
            int positionNum = 1000;
            int symbolNum = 3000;
            int positionPos = 0;
            int symbolPos = 0;
                    String msg = "";

            msg = "Insert into position.";
            System.out.println(msg);

            for (int i = 0; i < productNum; i++) {
                stmt.setString(1, "product" + i);
                positionPos = rand.nextInt(symbolNum);
                for (int j = 0; j < positionNum; j++) {
                    symbolPos = ((positionPos+j)%symbolNum);
                    stmt.setString(2, "symbol" + symbolPos);
                    stmt.setString(3, "industry" + symbolPos%industryNum);
                    stmt.setInt(4, 1000);
                    stmt.executeUpdate();
                }
            }

            msg = "Insert into position finished.";
            System.out.println(msg);
        }

    }
    public void updatePositionTableOneRow(String product,String symbol,int volume,int repeatNum) throws SQLException {
        long startNanoTime = 0;
        long finishNanoTime =0;
        double ms = 0.0;
        double speed = 0.0;
        long seed = System.currentTimeMillis();
        String msg = "";
        try (PreparedStatement stmt =
                     conn.prepareStatement("Update Position set volume = ? Where product = ? and symbol = ? ")) {

            startNanoTime = System.nanoTime();
            for (int i = 0; i < repeatNum; i++) {
             stmt.setInt(1, volume);
             stmt.setString(2, product);
             stmt.setString(3, symbol);
             stmt.executeUpdate();
            }
            finishNanoTime = System.nanoTime();

            ms = finishNanoTime-startNanoTime==0 ? 1/1000000.0 : (finishNanoTime-startNanoTime)/1000000.0;
            speed = Math.round(repeatNum*100000.0/ms)/100.0;
            msg = "update PositionTableOneRow "+repeatNum+" times, total time spent: "+String.format("%010.2f", ms)+" ms "+"speed: "+speed+"/s";

            System.out.println(msg);
        }


    }
    public void updatePositionTable() throws SQLException {

        int productNum = 1000;
        int industryNum = 60;
        int positionNum = 1000;
        int symbolNum = 3000;
        int symbolPos = 0;
        int volume = 0;

        long startNanoTime = 0;
        long finishNanoTime =0;
        double ms = 0.0;
        long seed = System.currentTimeMillis();
        String msg = "";

        Random rand = new Random(seed);
        msg = "update position.";
        System.out.println(msg);

        try (PreparedStatement stmt =
                     conn.prepareStatement("Update Position set volume = ? Where symbol = ? and product = ? ")) {
            startNanoTime = System.nanoTime();
            // 在每个product中随机更新一个symbol的volume
            for (int j = 0; j < productNum; j++) {
                symbolPos = rand.nextInt(symbolNum);
                volume = rand.nextInt(10000)+1000;
                stmt.setDouble(1, volume);
                stmt.setString(2,"symbol"+symbolPos);
                stmt.setString(3, "product"+j);

                stmt.executeUpdate();
            }
            finishNanoTime = System.nanoTime();
            ms = finishNanoTime-startNanoTime==0 ? 1/1000000.0 : (finishNanoTime-startNanoTime)/1000000.0;
            msg = "update PositionTable finished, total time spent: "+String.format("%010.2f", ms)+" ms";
            System.out.println(msg);
        }
    }
    public void updatePositionTable1() throws SQLException {
        int productNum = 1000;
        int industryNum = 60;
        int positionNum = 1000;
        int symbolNum = 3000;
        int productPos = 0;
        int volume = 0;

        long startNanoTime = 0;
        long finishNanoTime =0;
        double ms = 0.0;
        long seed = System.currentTimeMillis();
        String msg = "";

        Random rand = new Random(seed);
        msg = "update position.";
        System.out.println(msg);

        try (PreparedStatement stmt =
                     conn.prepareStatement("Update Position set volume = ? Where symbol = ? and product = ?")) {
            startNanoTime = System.nanoTime();
            // 对每个symbol，随机更新一个product中的volume
            for (int j = 0; j < symbolNum; j++) {
                volume = rand.nextInt(10000)+1000;
                productPos = rand.nextInt(productNum);
                stmt.setDouble(1, volume);
                stmt.setString(2, "symbol" + j);
                stmt.setString(3, "product"+productPos);
                stmt.executeUpdate();
            }
            finishNanoTime = System.nanoTime();
            ms = finishNanoTime-startNanoTime==0 ? 1/1000000.0 : (finishNanoTime-startNanoTime)/1000000.0;
            //speed = Math.round(num*100000/ms)/100.0;
            msg = "update PositionTable1 finished , total time spent: "+String.format("%010.2f", ms)+" ms";
            System.out.println(msg);
        }

        //conn.close();
    }



    public static void main(String[] args) throws SQLException, InterruptedException  {

        SQLOperation sqlOper = new SQLOperation();

        sqlOper.createPositionTable();
        sqlOper.insertToPositionTable();
       // sqlOper.updatePositionTable();
       // sqlOper.updatePositionTable1();

       // sqlOper.updatePositionTableOneRow("product1","symbol1681",100000,1000);


          sqlOper.createPriceTable();
          sqlOper.insertToPriceTable();
       //  sqlOper.updatePriceTable();

        Thread updateTable = new Thread(){
            @Override
            public void run() {
                while (true) {

                    try {
                        sqlOper.updatePositionTable();
                        sqlOper.updatePriceTable();
                    } catch (SQLException e) {
                        System.out.println(e.getMessage());
                    }
                }
            }
        };
      //  updateTable.start();
      //  updateTable.join();
      //  updateTablePrice.start();

        //sqlOper.closeConnection();
    }

}
